1. Technical Field
The embodiments herein generally relate to database reporting, and, more particularly, to the process of data aggregation and presentation.
2. Description of the Related Art
There are several techniques of retrieving useful information from a multitude of detailed data in a computer database or any other data repository. Sometimes, when the data is limited, looking through all of the detailed data is sufficient. However, when the amount of data is large in the order thousands or millions or larger number of records, looking through detailed data may not be effective or useful. Detailed data can be aggregated based on information needs and domain expertise so it can be better managed and understood. Finally, depending on the type of data, different tabular or graphical presentations can be selected to review and understand the data. Given multiple presentations the user can quickly glance and choose the presentation that best suits the user's needs.
Conventional solutions to aggregating detailed data include: (1) the use of database metadata such as dimensional columns of summary level data or multi-dimensional databases (MDDBs); (2) the use of database metadata in lookup values of database key information; for example, aggregating on Product Description or Product ID; and (3) the use of relying on the grouping clause of the structured query language (SQL). Generally, these methods are sufficient to aggregate detailed data for user queries such as “Sales by Product”. Most important, these methods require a SQL query indicating which attributes have to be grouped. For instance, it is fairly obvious to someone skilled in the art to take a SQL such as with the explicit grouping information such as “Select AccountID, ProductID, ProductDescription, Sales from Table(s) Group By Product” and produce reports.
However, these methods can aggregate detailed data when the SQL query input to the system is generic such as “Select AccountID, ProductID, ProductDescription, Sales from Table” and no additional information from user or database metadata is available. Another example of a challenge is with a user query such as “Revenue by Sales” where the result set is large for manual review and no meta information is available. Such a query may be converted to “Select Revenue, AccountID from Table(s) Group By Sales”. If the result set has thousands of records and “Sales” has 5,000 unique values, then the aggregate result will have 5,000 records, which may not meaningful or useful or timely for understanding business information. Accordingly, there remains a need for a novel system and method for aggregating and presenting data to a user that overcomes the limitations of the conventional approaches.